﻿using System;
using ZPTJCX.Model;
using System.Data;
using System.Data.OracleClient;

namespace ZPTJCX.DAL
{
    public class ZPCLXXDAL
    {
        private ZPCLXX ToCustomer(DataRow row)
        {
            ZPCLXX zpclxx = new ZPCLXX();

            zpclxx.Zpnum = Convert.ToString(row[0]);
            zpclxx.Zpr = Convert.ToString(row[1]);

            return zpclxx;
        }
        private ZPCLXX ToCustomer_GetAll(DataRow row)
        {
            ZPCLXX zpclxx = new ZPCLXX();

            zpclxx.Zpr = Convert.ToString(row["zpr"]);
            zpclxx.Zpsj = Convert.ToDateTime(row["zpsj"]);
            zpclxx.Hphm = Convert.ToString(row["hphm"]);
            zpclxx.Zpdd = Convert.ToString(row["wfdd"]);

            return zpclxx;
        }

        public ZPCLXX[] GetAll(DateTime zpsj)
        {
            ZPCLXX[] zpclxxs = null;
            try
            {
	            DataTable table = new DataTable();
                table = SqlHelper.ExecuteDataTable(@"select count(*) AA, zpr from zpclxx
	                        where zpsj like :zpsj group by zpr",
                            new OracleParameter(":zpsj", zpsj));
                zpclxxs = new ZPCLXX[table.Rows.Count];
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow row = table.Rows[i];
                    zpclxxs[i] = ToCustomer(row);
                }
            }
            catch (System.Exception ex)
            {
                LogHelper.WriteLog(typeof(ZPCLXXDAL), ex, ex.Message);

            }
            return zpclxxs;
        }

        public ZPCLXX[] GetAll(DateTime zpsj, string scbj)
        {
            DataTable table = new DataTable();
            table = SqlHelper.ExecuteDataTable(@"select count(*) AA, zpr from zpclxx
                        where zpsj like :zpsj and scbj=:scbj group by zpr",
                        new OracleParameter(":zpsj", zpsj),
                        new OracleParameter(":scbj", scbj));
            ZPCLXX[] zpclxxs = new ZPCLXX[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                zpclxxs[i] = ToCustomer(row);
            }
            return zpclxxs;
        }
        public ZPCLXX[] GetAll(string zpr, DateTime zpsj, string scbj)
        {
            DataTable table = new DataTable();
            table = SqlHelper.ExecuteDataTable(@"select * from zpclxx
                        where zpsj like :zpsj and scbj=:scbj and zpr=:zpr",
                        new OracleParameter(":zpsj", zpsj),
                        new OracleParameter(":scbj", scbj),
                        new OracleParameter(":zpr", zpr));
            ZPCLXX[] zpclxxs = new ZPCLXX[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                zpclxxs[i] = ToCustomer_GetAll(row);
            }
            return zpclxxs;
        }

        public string GetById(string zpsj, string zpr)
        {
            DataTable dt = SqlHelper.ExecuteDataTable(@"select count(*) from zpclxx 
                    where zpsj=:zpsj and zpr=:zpr",
                new OracleParameter(":zpsj", zpsj),
                new OracleParameter(":zpr", zpr));
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else if (dt.Rows.Count > 1)
            {
                throw new Exception("严重错误，查出多条数据！");
            }
            else
            {
                DataRow row = dt.Rows[0];
                string value = row[0].ToString();
                return value;
            }
        }
        public string GetById(string zpsj, string zpr, string scbj)
        {
            DataTable dt = SqlHelper.ExecuteDataTable(@"select count(*) from zpc3lxx 
                    where zpsj=:zpsj and zpr=:zpr and scbj=:scbj",
                new OracleParameter(":zpsj", zpsj),
                new OracleParameter(":zpr", zpr),
                new OracleParameter(":scbj", scbj));
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else if (dt.Rows.Count > 1)
            {
                throw new Exception("严重错误，查出多条数据！");
            }
            else
            {
                DataRow row = dt.Rows[0];
                string value = row[0].ToString();
                return value;
            }
        }
        //public int Insert(ZPCLXX zpclxx)
        //{
        //    int returnValue = 0;
        //    //to_date(:JGSJ,'yyyy-mm-dd hh24:mi:ss')
        //    returnValue = SqlHelper.ExecuteNonQuery(@"INSERT INTO zpclxx
        //                (HPHM, WFDD, ZPSJ, ZPR)
        //         VALUES
        //               (:HPHM, :WFDD, :ZPSJ, :ZPR)",

        //        new OracleParameter(":HPHM", zpclxx.Hphm),
        //        new OracleParameter(":WFDD", zpclxx.Wfdd),
        //        new OracleParameter(":ZPSJ", zpclxx.Zpsj),

        //        new OracleParameter(":ZPR", zpclxx.Zpr));
        //    return returnValue;

        //}

    }
}
